﻿//+---------------------------------------------------------------------------
//
// Copyright (C) Microsoft Corporation, 2008
//
// File: ServiceDbDataSet.cs
//
// Contents: Extension database methods for accessing internal DB state.
//
//
// Created by: waits 2008-10-21
//
//----------------------------------------------------------------------------


using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;


namespace Microsoft.Reputation.Tests.FrontEndCommon
{
    public class DbProcs : IDisposable
    {
        #region private_members

        private SqlConnection m_connection;
        private SqlCommand[] m_commandCollection;
        private string m_connectionStr;

        #endregion

        #region private_methods

        private void InitConnection()
        {
            m_connection = new SqlConnection();
            m_connection.ConnectionString = m_connectionStr;
        }

        private void InitCommandCollection()
        {
            this.m_commandCollection = new SqlCommand[5];

            this.m_commandCollection[0] = new SqlCommand();
            this.m_commandCollection[0].Connection = this.Connection;
            this.m_commandCollection[0].CommandText = "Private.prc_getRandomObjects";
            this.m_commandCollection[0].CommandType = CommandType.StoredProcedure;
            this.m_commandCollection[0].Parameters.Add(new SqlParameter("@objectType", SqlDbType.TinyInt, 0, ParameterDirection.Input, false, 0, 0, "objectType", DataRowVersion.Current, null));
            this.m_commandCollection[0].Parameters.Add(new SqlParameter("@countObjects", SqlDbType.Int, 0, ParameterDirection.Input, false, 0, 0, "countObjects", DataRowVersion.Current, null));
            this.m_commandCollection[0].Parameters.Add(new SqlParameter("@objectsAlreadySeenXML", SqlDbType.Xml, 0, ParameterDirection.Input, true, 0, 0, "objectsAlreadySeenXML", DataRowVersion.Current, null));

            this.m_commandCollection[1] = new SqlCommand();
            this.m_commandCollection[1].Connection = this.Connection;
            this.m_commandCollection[1].CommandText = "IF EXISTS ( SELECT TOP 1 1 FROM WebService.[Object] WHERE [ObjectGuid] = @guid ) BEGIN SELECT 1 AS [exists] END ELSE BEGIN SELECT 0 AS [exists] END";
            this.m_commandCollection[1].CommandType = CommandType.Text;
            this.m_commandCollection[1].Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, false, 0, 0, "guid", DataRowVersion.Current, null));

            this.m_commandCollection[2] = new SqlCommand();
            this.m_commandCollection[2].Connection = this.Connection;
            this.m_commandCollection[2].CommandText = "SELECT Private.fns_defaultExpirationMins() as [Expr1]";
            this.m_commandCollection[2].CommandType = CommandType.Text;

            this.m_commandCollection[3] = new SqlCommand();
            this.m_commandCollection[3].Connection = this.Connection;
            this.m_commandCollection[3].CommandText = "SELECT Private.fns_defaultNoExpiration() as [Expr1]";
            this.m_commandCollection[3].CommandType = CommandType.Text;

            this.m_commandCollection[4] = new SqlCommand();
            this.m_commandCollection[4].Connection = this.Connection;
            this.m_commandCollection[4].CommandText = "SELECT COUNT([ObjectId]) FROM WebService.[Object] WHERE [ObjectTypeId] = @type";
            this.m_commandCollection[4].CommandType = CommandType.Text;
            this.m_commandCollection[4].Parameters.Add(new SqlParameter("@type", SqlDbType.SmallInt, 0, ParameterDirection.Input, false, 0, 0, "type", DataRowVersion.Current, null));
        }

        private string BuildGuidListXml(IList<Guid> guidList)
        {
            StringBuilder strRes = new StringBuilder();

            strRes.Append("<GUIDs>");
            if (guidList != null)
            {
                foreach (Guid curGuid in guidList)
                {
                    strRes.AppendFormat("<GUID>{0}</GUID>", curGuid.ToString().ToUpper());
                }
            }
            strRes.Append("</GUIDs>");

            return strRes.ToString();
        }

        #endregion

        #region properties

        private SqlCommand[] CommandCollection
        {
            get
            {
                return m_commandCollection;
            }
        }

        private SqlConnection Connection
        {
            get
            {
                return m_connection;
            }
        }

        #endregion

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connectionStr"></param>
        public DbProcs(string connectionStr)
        {
            if (String.IsNullOrEmpty(connectionStr))
            {
                throw new ArgumentException("connectionStr", "Cannot use null or empty connection string.");
            }

            m_connectionStr = connectionStr;
            InitConnection();
            InitCommandCollection();
        }

        /// <summary>
        /// Returns a list of Random Guids from the Service DB for the given object type
        /// </summary>
        /// <param name="objType">Object Type for which GUIDs are needed. Eg: File, Application, Publisher, URL</param>
        /// <param name="count">Number for GUIDs to be returned</param>
        /// <param name="prevObjs">List of GUIDs already selected. When passed these GUIDs are not added again to the list from random selection</param>
        /// <param name="remaining">Number of unique objects for the given type remaining in the database</param>
        /// <returns>List of Random Guids for the given object type</returns>
        public IList<Guid> GetRandomObjects(byte objType, int count, IList<Guid> prevObjs, out int remaining)
        {
            // Has the command to execute stored procedure Private.prc_getRandomObjects
            SqlCommand cmd = this.CommandCollection[0];

            ConnectionState prevConnectionState = cmd.Connection.State;
            if ((cmd.Connection.State & ConnectionState.Open) != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }

            remaining = 0;

            SqlDataReader resReader;
            List<Guid> resList = new List<Guid>();
            try
            {
                cmd.Parameters["@objectType"].Value = objType;
                cmd.Parameters["@countObjects"].Value = count;
                cmd.Parameters["@objectsAlreadySeenXML"].Value = BuildGuidListXml(prevObjs);

                resReader = cmd.ExecuteReader();

                // First row has the number of remaining objects for the given type
                while (resReader.Read())
                {
                    remaining = resReader.GetInt32(0);
                }

                // Next row has the list of Guids
                if (resReader.NextResult())
                {
                    while (resReader.Read())
                    {
                        resList.Add(resReader.GetGuid(0));
                    }
                }
            }
            finally
            {
                if (prevConnectionState == ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                }
            }

            return resList;
        }

        public bool DoesGuidExist(Guid guid)
        {
            SqlCommand cmd = this.CommandCollection[1];

            ConnectionState prevConnectionState = cmd.Connection.State;
            if ((cmd.Connection.State & ConnectionState.Open) != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }

            try
            {
                cmd.Parameters["@guid"].Value = guid;

                object oRes = cmd.ExecuteScalar();

                if ((oRes == null) || (oRes.GetType() == typeof(DBNull)))
                {
                    return false;
                }
                else
                {
                    return (((int)(oRes)) == 1);
                }
            }
            finally
            {
                if (prevConnectionState == ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                }
            }
        }

        public int GetDefaultExpirationMins()
        {
            SqlCommand cmd = this.CommandCollection[2];

            ConnectionState prevConnectionState = cmd.Connection.State;
            if ((cmd.Connection.State & ConnectionState.Open) != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }

            try
            {
                object oRes = cmd.ExecuteScalar();

                if ((oRes == null) || (oRes.GetType() == typeof(DBNull)))
                {
                    return 0;
                }
                else
                {
                    return ((int)(oRes));
                }
            }
            finally
            {
                if (prevConnectionState == ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                }
            }
        }

        public bool GetDefaultNoExpiration()
        {
            SqlCommand cmd = this.CommandCollection[3];

            ConnectionState prevConnectionState = cmd.Connection.State;
            if ((cmd.Connection.State & ConnectionState.Open) != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }

            try
            {
                object oRes = cmd.ExecuteScalar();

                if ((oRes == null) || (oRes.GetType() == typeof(DBNull)))
                {
                    return false;
                }
                else
                {
                    return bool.Parse(((string)(oRes)));
                }
            }
            finally
            {
                if (prevConnectionState == ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                }
            }
        }

        public int GetTypeCount(Int16 type)
        {
            SqlCommand cmd = this.CommandCollection[4];

            ConnectionState prevConnectionState = cmd.Connection.State;
            if ((cmd.Connection.State & ConnectionState.Open) != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }

            try
            {
                cmd.Parameters["@type"].Value = type;

                object oRes = cmd.ExecuteScalar();

                if ((oRes == null) || (oRes.GetType() == typeof(DBNull)))
                {
                    return 0;
                }
                else
                {
                    return ((int)(oRes));
                }
            }
            finally
            {
                if (prevConnectionState == ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                }
            }
        }


        #region IDisposable Members
        ~DbProcs()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);

            GC.SuppressFinalize(this);
        }

        private void Dispose(bool isDisposing)
        {
            if (isDisposing)
            {
                if (m_connection != null)
                {
                    m_connection.Dispose();
                    m_connection = null;
                }

                if (m_commandCollection != null)
                {
                    for (int i = 0; i < m_commandCollection.Length; i++)
                    {
                        m_commandCollection[i].Dispose();
                        m_commandCollection[i] = null;
                    }

                    m_commandCollection = null;
                }
            }
        }
        #endregion
    }
}
